In this article we are going see the SQLite WHERE clause that is used to specify a condition while fetching the data from one table or multiple tables.

If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing. Rows are excluded from the result if the WHERE clause evaluates to either false or NULL. 

The WHERE clause not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would study in subsequent chapters.

Syntax: The basic syntax of SQLite SELECT statement with WHERE clause is as follows:

 Example: We can specify a condition using Comparison or Logical Operators like >, <, =, LIKE, NOT, etc. Consider COMPANY table has the following records:

Here are simple examples showing usage of SQLite Logical Operators. Following

SELECT statement lists down all the records where AGE is greater than or equal to

25 AND salary is greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; 

Following SELECT statement lists down all the records where AGE is greater than or

equal to 25 OR salary is greater than or equal to 65000.00:

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

Following SELECT statement lists down all the records where AGE is not NULL

which means all the records because none of the record is having AGE equal to

NULL:

sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

Following SELECT statement lists down all the records where NAME starts with 'Pr',

does not matter what comes after 'Pr'.

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Pr%';

Following SELECT statement lists down all the records where NAME starts with 'Ki',

does not matter what comes after 'Ki':

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Pr*';

Following SELECT statement lists down all the records where AGE value is either 25 or 27:

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

Following SELECT statement lists down all the records where AGE value is neither

25 nor 27:

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

Following SELECT statement lists down all the records where AGE value is in

BETWEEN 25 AND 27:

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

Following SELECT statement makes use of SQL sub-query where sub-query finds all

the records with AGE field having SALARY > 65000 and later WHERE clause is

being used along with EXISTS operator to list down all the records where AGE from

the outside query exists in the result returned by sub-query:

sqlite> SELECT AGE FROM COMPANY

        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

Following SELECT statement makes use of SQL sub-query where sub-query finds all

the records with AGE field having SALARY > 65000 and later WHERE clause is

being used along with > operator to list down all the records where AGE from

outside query is greater than the age in the result returned by sub-query:

sqlite> SELECT * FROM COMPANY

        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);